package cn.edu.zhku.citrusmonitor.law.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.BeanProcessor;
import org.apache.commons.dbutils.GenerousBeanProcessor;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.RowProcessor;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import cn.edu.zhku.citrusmonitor.domain.Bean;
import cn.edu.zhku.citrusmonitor.domain.Law;
import cn.edu.zhku.citrusmonitor.util.C3P0Util;

public class LawDaoImpl implements LawDao{
	
	BeanProcessor bean=new GenerousBeanProcessor();
	RowProcessor processor=new BasicRowProcessor(bean);
	@Override
	public void addLawByBean(Law law) throws SQLException {
		// TODO Auto-generated method stub
		QueryRunner qr=new QueryRunner(C3P0Util.getDataSource());
		String sql="insert into law(law_id,tittle,source,content,file_path) values(?,?,?,?,?)";
		
		qr.update(sql,law.getLawId(),law.getTittle(),law.getSource(),law.getContent(),law.getFilePath());
		
	}
	@Override
	public List<Law> getLawByTittle(String tittle) throws SQLException {
		// TODO Auto-generated method stub
		QueryRunner qr=new QueryRunner(C3P0Util.getDataSource());
		String sql="select * from law where tittle like ?";
		return qr.query(sql, new BeanListHandler<Law>(Law.class,processor),"%"+tittle+"%");
		
	}
	@Override
	public long getCounts() throws SQLException {
		// TODO Auto-generated method stub
		QueryRunner qr=new QueryRunner(C3P0Util.getDataSource());
		return (long)qr.query("select count(*) from law", new ScalarHandler(1));
		
	}
	@Override
	public List<Law> getList(int currentPage, int pageSize) throws SQLException {
		// TODO Auto-generated method stub
		QueryRunner qr=new QueryRunner(C3P0Util.getDataSource());
		String sql="select * from law limit ?,?";
		
		return qr.query(sql,new BeanListHandler<Law>(Law.class,processor),(currentPage-1)*pageSize,pageSize);
	}
	@Override
	public Law getLawById(String id) throws SQLException {
		// TODO Auto-generated method stub
		QueryRunner qr=new QueryRunner(C3P0Util.getDataSource());
		String sql="select * from law where law_id=?";
		return qr.query(sql, new BeanHandler<Law>(Law.class,processor),id);		
	}
	@Override
	public void updateLaw(Law law) throws SQLException {
		// TODO Auto-generated method stub
		QueryRunner qr=new QueryRunner(C3P0Util.getDataSource());
		
		List<String> list=new ArrayList<String>();
		String sql="update law set law_id=?";
		list.add(law.getLawId());
		if(!law.getTittle().trim().equals("")&&law.getTittle()!=null) {
			sql+=",tittle=?";
			list.add(law.getTittle());
		}
		if(!law.getSource().trim().equals("")&&law.getSource()!=null) {
			sql+=",source=?";
			list.add(law.getSource());
		}
		if(law.getPublishTime()!=null) {
			sql+=",publish_time=?";
			list.add(law.getPublishTime().toString());
		}
		if(law.getContent()!=null&&!law.getContent().trim().equals("")) {
			sql+=",content=?";
			list.add(law.getContent());
		}
		if(law.getFilePath()!=null&&!law.getFilePath().trim().equals("")) {
			sql+=",file_path=?";
			list.add(law.getFilePath());
		}
		sql+=" where law_id=?";
		list.add(law.getLawId());
		
		qr.update(sql,list.toArray());
	}
	/**
	 * 获取部分信息的对象集合
	 * @throws SQLException 
	 */
	@Override
	public List<Law> getList(String pageSize) throws SQLException {
		// TODO Auto-generated method stub
		QueryRunner qr=new QueryRunner(C3P0Util.getDataSource());
		String sql="select law_id,tittle,publish_time from law order by publish_time desc limit 0,?";
		
		
		return qr.query(sql, new BeanListHandler<Law>(Law.class,processor),Integer.parseInt(pageSize));
		
	}
	@Override
	public List<Bean> getWebList(int currentPage, int pageSize) throws SQLException {
		// TODO Auto-generated method stub
		QueryRunner qr=new QueryRunner(C3P0Util.getDataSource());
		String sql="select law_id as id,tittle as title,publish_time as time from law limit ?,?";
		return qr.query(sql, new BeanListHandler<Bean>(Bean.class,processor),(currentPage-1)*pageSize,pageSize);
		
	}
	@Override
	public Long getWebCounts() throws SQLException {
		// TODO Auto-generated method stub
		QueryRunner qr=new QueryRunner(C3P0Util.getDataSource());
		String sql="select count(*) from law";
		return (long)qr.query(sql, new ScalarHandler(1));
	}
	@Override
	public int delete(String id) throws SQLException {
		// TODO Auto-generated method stub
		QueryRunner qr=new QueryRunner(C3P0Util.getDataSource());
		return qr.update("delete from law where law_id=?",id);
		
	}
	@Override
	public Bean selectDetail(String id) throws SQLException {
		// TODO Auto-generated method stub
		QueryRunner qr=new QueryRunner(C3P0Util.getDataSource());
		String sql="select law_id as id,tittle as title,publish_time as time,source as distribution,file_path as filePath,content from law where law_id =?";
		return qr.query(sql,new BeanHandler<Bean>(Bean.class,processor),id );
		
	}
	@Override
	public int[] removeAllById(String[] ids) throws SQLException {
		// TODO Auto-generated method stub
		QueryRunner qr=new QueryRunner(C3P0Util.getDataSource());
		String sql="delete from law where law_id=?";
		String[][] values=new String[ids.length][];
		for(int i=0;i<ids.length;i++) {
			values[i]=new String[] {ids[i]};
		}
		
		return qr.batch(sql, values);
		
	}
	
}
